Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# BITAND

Syntax

Description of the illustration bitand.gif

Purpose

`BITAND` computes an `AND` operation on the bits of `expr1` and `expr2`, both of which must resolve to nonnegative integers, and returns an integer. This function is commonly used with the `DECODE` function, as illustrated in the example that follows.

An `AND` operation compares two bit values. If the values are the same, the operator returns 1. If the values are different, the operator returns 0. Only significant bits are compared. For example, an `AND` operation on the integers 5 (binary `101`) and 1 (binary `001` or `1`) compares only the rightmost bit, and results in a value of 1 (binary `1`).

Both arguments can be any numeric datatype, or any nonnumeric datatype that can be implicitly converted to `NUMBER`. The function returns `NUMBER`.

Note: This function does not determine the datatype of the value returned. Therefore, in SQL*Plus, you must specify `BITAND` in a wrapper, such as `TO_NUMBER`, which returns a datatype.

See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Examples

The following represents each `order_status` in the sample table `oe.orders` by individual bits. (The example specifies options that can total only 7, so rows with `order_status` greater than 7 are eliminated.)

    SELECT order_id, customer_id,
      DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice')
          Location,
      DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air') Method,
      DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified') Receipt
      FROM orders
      WHERE order_status < 8;

       ORDER_ID CUSTOMER_ID LOCATION   METHOD RECEIPT
    ---------- ----------- ---------- ------ ---------
          2458         101 PostOffice Air    Certified
          2397         102 Warehouse  Air    Certified
          2454         103 Warehouse  Air    Certified
          2354         104 PostOffice Air    Certified
          2358         105 PostOffice Ground Certified
          2381         106 Warehouse  Ground Certified
          2440         107 Warehouse  Ground Certified
          2357         108 Warehouse  Air    Insured
          2394         109 Warehouse  Air    Insured
          2435         144 PostOffice Ground Insured
          2455         145 Warehouse  Ground Insured
          2356         105 Warehouse  Air    Insured
          2360         107 PostOffice Air    Insured
          ...